SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



-- =============================================
-- Create View template
-- =============================================



CREATE VIEW [dbo].[PSFormulas] AS

SELECT  

		CASE WHEN 
			llraw.[Transaction Type] = 'SELL' 			THEN  llRaw.[Fund ID] + '-' + CAST(CAST(llRaw.[Lot Number - Buy Lot] AS INT) AS NVARCHAR(50))
			ELSE CASE WHEN
            
		CAST(llRaw.[Account Number] AS  INT) = 0 THEN llRaw.[Fund ID] + '-' + CAST(CAST(llRaw.[Lot Number - Buy Lot] AS INT) AS NVARCHAR(50)) +'-' +LEFT(llRaw.[Asset ID],2)  ELSE llRaw.[Fund ID] +'-' + CAST(CAST(llRaw.[Account Number] AS INT)  AS NVARCHAR(20))
		END 

END SSID,
		
        llRaw.[Account Number] ,
               llRaw.[Account Number - Buy Lot] ,
               llRaw.[Account Period Change Date] ,
               llRaw.[Actual Settlement Date] ,
               llRaw.[Adjustment Indicator] ,
               llRaw.[Asset Class Code] ,
               llRaw.[Asset Group Code] ,
               llRaw.[Asset Group Name] ,
               llRaw.[Asset ID] ,
               llRaw.[Average Cost (Base)] ,
               llRaw.[Average Cost (Local)] ,
               llRaw.[Average Currency Gn/Ls] ,
               llRaw.[Base 5 Year Gain/Loss Amount] ,
               llRaw.[Base Amortization Sold] ,
               llRaw.[Base Amortization Sold - Buy Lot] ,
               llRaw.[Base Currency Code] ,
               llRaw.[Base Currency Gain] ,
               llRaw.[Base Currency Gain/Loss] ,
               llRaw.[Base Currency Loss] ,
               llRaw.[Base Fees] ,
               llRaw.[Base Long Term Gain/Loss - Buy Lot] ,
               llRaw.[Base Net Proceed Amount] ,
               llRaw.[Base Original Average Cost] ,
               llRaw.[Base Original Identified Cost] ,
               llRaw.[Base Realized Gain] ,
               llRaw.[Base Realized Gain/Loss] ,
               llRaw.[Base Realized Loss] ,
               llRaw.[Base Security Gain/Loss] ,
               llRaw.[Base Short Term Gain/Loss - Buy Lot] ,
               llRaw.[Bifurcation Flag] ,
               llRaw.[Bloomberg Expanded Ticker] ,
               llRaw.[Bloomberg Ticker] ,
               llRaw.Broker ,
               llRaw.[Broker Number] ,
               llRaw.[Broker Reason Code] ,
               llRaw.[Call/Put Indicator] ,
               llRaw.[Cancel Post Date] ,
               llRaw.[Cancel Reason Code] ,
               llRaw.[Cancel Report Date] ,
               llRaw.[Cancel Trade ID] ,
               llRaw.Canceled ,
               llRaw.[Cash Broker Flag] ,
               llRaw.[Cash Costing Code] ,
               llRaw.[Cash Cusip Flag] ,
               llRaw.[Cash Reason Code] ,
               llRaw.[Client Fund Reference Number] ,
               llRaw.[Client ID] ,
               llRaw.[Client Name] ,
               llRaw.[Closing Date] ,
               llRaw.[Closing Price] ,
               llRaw.[Commission (Base)] ,
               llRaw.[Commission (Local)] ,
               llRaw.[Commission Per Share] ,
               llRaw.[Contractual Settle Date] ,
               llRaw.[Contributed Property Flag] ,
               llRaw.[Contributing ID] ,
               llRaw.[Corporate Action Broker Flag] ,
               llRaw.[Costing Method] ,
               llRaw.[Country Code] ,
               llRaw.[Country of Risk Code] ,
               llRaw.[Currency 5 Year Gain/Loss] ,
               llRaw.[Current Multiplier] ,
               llRaw.[Days Past Due] ,
               llRaw.[Days to Maturity] ,
               llRaw.[Delay Date] ,
               llRaw.[Delay Date Flag] ,
               llRaw.[Delay Days] ,
               llRaw.[Detail Transaction Type] ,
               llRaw.[Dirty Shares Adjusted] ,
               llRaw.[Dirty Shares Sold] ,
               llRaw.[Dummy Cusip Indicator] ,
               llRaw.[ERISA Regulatory Cost] ,
               llRaw.[Effective Date] ,
               llRaw.[Exchange Rate - Buy Lot] ,
               llRaw.[Exchange Rate Source] ,
               llRaw.[Exchange Ticker] ,
               llRaw.[Expanded Broker Reason Code] ,
               llRaw.[FX Type] ,
               llRaw.[Fees/Other (Base)] ,
               llRaw.[Fees/Other (Local)] ,
               llRaw.[First Payment Date] ,
               llRaw.[Fiscal Year] ,
               llRaw.[Foreign Indicator] ,
               llRaw.[Free to Distribute Date] ,
               llRaw.[Fund ID] ,
               llRaw.[Fund Name] ,
               llRaw.[Fund Type Code] ,
               llRaw.[Fund of Funds Indicator] ,
               llRaw.[Gain Loss Method Indicator] ,
               llRaw.[ID Cost (Base)] ,
               llRaw.[ID Cost (Local)] ,
               llRaw.[ID Cost Base - Buy Lot] ,
               llRaw.[ID Cost Local - Buy Lot] ,
               llRaw.[ISIN Number] ,
               llRaw.[In Kind Flag] ,
               llRaw.[In Kind Trade Fees Flag] ,
               llRaw.[In Kind Transaction Code] ,
               llRaw.[Income Currency Code] ,
               llRaw.[Interest (Base)] ,
               llRaw.[Interest (Local)] ,
               llRaw.[Interest Rate] ,
               llRaw.[Investment Type] ,
               llRaw.[Investment Type Code] ,
               llRaw.Investor ,
               llRaw.[Issue Country Code] ,
               llRaw.[LT Currency Gain/Loss] ,
               llRaw.[LT Currency Gain/Loss - Buy Lot] ,
               llRaw.[Last Closing Date] ,
               llRaw.[Ledger Composite Fund] ,
               llRaw.[Local 5 Year Gain/Loss Amount] ,
               llRaw.[Local Amortization Sold] ,
               llRaw.[Local Amortization Sold - Buy Lot] ,
               llRaw.[Local Currency] ,
               llRaw.[Local Currency Name] ,
               llRaw.[Local Fees] ,
               llRaw.[Local Long Term Gain/Loss - Buy Lot] ,
               llRaw.[Local Net Proceed Amount] ,
               llRaw.[Local Net Settlement] ,
               llRaw.[Local Original Average Cost] ,
               llRaw.[Local Original Identified Cost] ,
               llRaw.[Local Realized Gain] ,
               llRaw.[Local Realized Gain/Loss] ,
               llRaw.[Local Realized Loss] ,
               llRaw.[Local Security Gain/Loss] ,
               llRaw.[Local Short Term Gain/Loss - Buy Lot] ,
               llRaw.[Long Short Indicator] ,
               llRaw.[Long Term Gain/Loss (Base)] ,
               llRaw.[Long Term Gain/Loss (Local)] ,
               llRaw.[Lot Number] ,
               llRaw.[Lot Number - Buy Lot] ,
               llRaw.[Major Industry Code] ,
               llRaw.[Major Industry Name] ,
               llRaw.[Manager Name] ,
               llRaw.[Maturity Date] ,
               llRaw.[Minor Industry Code] ,
               llRaw.[Minor Industry Name] ,
               llRaw.[Moody's Quality Rating] ,
               llRaw.[NAV Day Date] ,
               llRaw.[Net Amount (Base)] ,
               llRaw.[Net Amount (Local)] ,
               llRaw.[Net Gain/Loss (ID Cost - Base)] ,
               llRaw.[Net Gain/Loss (ID Cost - Local)] ,
               llRaw.[Net Trade Indicator] ,
               llRaw.[Option Expiration Date] ,
               llRaw.[Orig Trade Date] ,
               llRaw.[Original Face] ,
               llRaw.[Original Interest Rate] ,
               llRaw.[Original Maturity Date] ,
               llRaw.[Original Multiplier] ,
               llRaw.[Original Trade Date - Buy Lot] ,
               llRaw.[Original Trade ID] ,
               llRaw.[Original Transaction Type] ,
               llRaw.[Payment Frequency Code] ,
               llRaw.[Pool ID Number] ,
               llRaw.[Pool Type Code] ,
               llRaw.[Post Date] ,
               llRaw.[Price (Base)] ,
               llRaw.[Price (Local)] ,
               llRaw.[Price Source] ,
               llRaw.[Principal Trade Switch] ,
               llRaw.[Realized Gain/Loss (Average Cost - Base)] ,
               llRaw.[Realized Gain/Loss (Average Cost - Local)] ,
               llRaw.[Report Date] ,
               llRaw.[Restrict Lot Indicator] ,
               llRaw.[S and P Quality Rating] ,
               llRaw.[S3 Currency Gain/Loss] ,
               llRaw.[SSC Trade ID] ,
               llRaw.[ST Currency Gain/Loss] ,
               llRaw.[ST Currency Gain/Loss - Buy Lot] ,
               llRaw.[Security Description] ,
               llRaw.[Security Description 2] ,
               llRaw.[Security Name] ,
               llRaw.[Security Name/Description] ,
               llRaw.[Segregated Lot] ,
               llRaw.[Sell Exchange Rate] ,
               llRaw.[Settlement Comment] ,
               llRaw.[Settlement Currency] ,
               llRaw.[Settlement Date Exchange Rate] ,
               llRaw.[Settlement Location] ,
               llRaw.[Settlement Status] ,
               llRaw.[Shares/Par] ,
               llRaw.[Shares/Par - Buy Lot] ,
               llRaw.[Short Sell Indicator] ,
               llRaw.[Short Term Gain/Loss- Base] ,
               llRaw.[Short Term Gain/Loss- Local] ,
               llRaw.[Short3 Gain/Loss- Base] ,
               llRaw.[Short3 Gain/Loss- Local] ,
               llRaw.[State Code] ,
               llRaw.[Swap Fund Number] ,
               llRaw.[Tax Country Code] ,
               llRaw.[Taxes (Base)] ,
               llRaw.[Taxes (Local)] ,
               llRaw.Ticker ,
               llRaw.[Trade Country Code] ,
               llRaw.[Trade Currency] ,
               llRaw.[Trade Date] ,
               llRaw.[Transaction Code] ,
               llRaw.[Transaction Type] ,
               llRaw.[Transaction Type Detail] ,
               llRaw.[Update Date/Time]
	   
	    FROM  dbo.PSFull llRaw


GO
